import sqlite3
from datetime import datetime, timedelta
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Base, DailyRecord
from typing import Dict, List, Optional

# 导入产品名称映射
PRODUCT_NAME_MAPPING = {
    "aibangzhu": "爱帮主",
    "aiqinghua": "AI情话",
    "zhuiai": "追爱神器",
}


class DailyDataManager:
    """管理每日历史数据的类"""

    def __init__(self, db_path: str = "predictions.db"):
        self.db_path = db_path
        self.engine = create_engine(f"sqlite:///{db_path}")
        self.SessionLocal = sessionmaker(
            autocommit=False, autoflush=False, bind=self.engine
        )
        self._ensure_tables_exist()

    def _ensure_tables_exist(self):
        """确保所有表都存在"""
        Base.metadata.create_all(bind=self.engine)

    def is_yesterday_data_saved(self, product_name: str) -> bool:
        """检查昨天的数据是否已经保存"""
        yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")

        # 使用中文产品名称进行查询
        chinese_name = PRODUCT_NAME_MAPPING.get(product_name, product_name)

        with self.SessionLocal() as session:
            existing_record = (
                session.query(DailyRecord)
                .filter(
                    DailyRecord.date_str == yesterday,
                    DailyRecord.product_name == chinese_name,
                )
                .first()
            )
            return existing_record is not None

    def save_yesterday_data(
        self,
        product_name: str,
        last_order_sum: int,
        last_reg_count: int,
        last_order_count: int,
    ) -> bool:
        """保存昨天的数据，返回是否成功保存"""
        yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")

        # 转换为中文产品名称
        chinese_name = PRODUCT_NAME_MAPPING.get(product_name, product_name)

        # 检查是否已经存在
        if self.is_yesterday_data_saved(product_name):
            print(f"产品 {chinese_name} 的昨日数据({yesterday})已经存在，跳过插入")
            return False

        try:
            with self.SessionLocal() as session:
                daily_record = DailyRecord(
                    date_str=yesterday,
                    order_sum=last_order_sum,
                    reg_count=last_reg_count,
                    order_count=last_order_count,
                    product_name=chinese_name,  # 使用中文名称保存
                )
                session.add(daily_record)
                session.commit()
                print(
                    f"成功插入产品 {chinese_name} 的昨日数据({yesterday}): "
                    f"销售额={last_order_sum}, 注册数={last_reg_count}, 订单数={last_order_count}"
                )
                return True
        except Exception as e:
            print(f"插入昨日数据失败: {e}")
            return False

    def get_historical_data(self, product_name: str, days: int = 7) -> List[Dict]:
        """获取指定产品的历史数据"""
        # 转换为中文产品名称
        chinese_name = PRODUCT_NAME_MAPPING.get(product_name, product_name)

        with self.SessionLocal() as session:
            records = (
                session.query(DailyRecord)
                .filter(DailyRecord.product_name == chinese_name)
                .order_by(DailyRecord.date_str.desc())
                .limit(days)
                .all()
            )

            return [
                {
                    "date": record.date_str,
                    "order_sum": record.order_sum,
                    "reg_count": record.reg_count,
                    "order_count": record.order_count,
                    "created_at": record.created_at,
                }
                for record in records
            ]

    def get_recent_7days_stats(self, product_name: str) -> Dict:
        """获取近7日统计数据"""
        # 转换为中文产品名称
        chinese_name = PRODUCT_NAME_MAPPING.get(product_name, product_name)

        # 计算7天前的日期
        seven_days_ago = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")

        with self.SessionLocal() as session:
            records = (
                session.query(DailyRecord)
                .filter(
                    DailyRecord.product_name == chinese_name,
                    DailyRecord.date_str >= seven_days_ago,
                )
                .all()
            )

            if not records:
                # 如果没有历史数据，返回默认值
                return {
                    "recent_7days_payment_rate": 0.0,  # 近7日付费率
                    "recent_7days_arpu": 0.0,  # 近7日ARPU
                    "recent_7days_avg_revenue": 0,  # 近7日日均流水（整数显示）
                    "recent_7days_avg_order_value": 0.0,  # 近7日客单价
                    "recent_7days_avg_new_users": 0,  # 近7日日均新增用户
                    "actual_days": 0,  # 实际统计天数
                    "recent_7days_total_orders": 0,  # 近7日总订单数
                    "recent_7days_total_users": 0,  # 近7日总用户数
                    "recent_7days_total_revenue": 0,  # 近7日总收入
                }

            # 计算统计数据
            total_order_sum = sum(record.order_sum for record in records)
            total_reg_count = sum(record.reg_count for record in records)
            total_order_count = sum(record.order_count for record in records)
            actual_days = len(records)

            # 计算指标
            payment_rate = (
                (total_order_count / total_reg_count * 100)
                if total_reg_count > 0
                else 0.0
            )
            arpu = (total_order_sum / total_reg_count) if total_reg_count > 0 else 0.0
            avg_revenue = (total_order_sum / actual_days) if actual_days > 0 else 0.0
            # 计算近7日客单价 = 近7日总流水 / 近7日总订单数
            average_order_value = (
                (total_order_sum / total_order_count) if total_order_count > 0 else 0.0
            )
            # 计算近7日日均新增用户
            avg_new_users = (total_reg_count / actual_days) if actual_days > 0 else 0.0

            return {
                "recent_7days_payment_rate": round(payment_rate, 2),  # 近7日付费率 (%)
                "recent_7days_arpu": round(arpu, 2),  # 近7日ARPU
                "recent_7days_avg_revenue": int(
                    avg_revenue
                ),  # 近7日日均流水（整数显示）
                "recent_7days_avg_order_value": round(
                    average_order_value, 2
                ),  # 近7日客单价
                "recent_7days_avg_new_users": int(avg_new_users),  # 近7日日均新增用户
                "actual_days": actual_days,  # 实际统计天数
                "recent_7days_total_orders": total_order_count,  # 近7日总订单数
                "recent_7days_total_users": total_reg_count,  # 近7日总用户数
                "recent_7days_total_revenue": total_order_sum,  # 近7日总收入
            }

    def process_api_response_data(self, api_data: Dict, product_name: str) -> bool:
        """处理API响应数据，提取昨日数据并保存"""
        try:
            # 从API响应中提取昨日数据
            last_order_sum = int(api_data.get("last_order_sum", 0))
            last_reg_count = int(api_data.get("last_reg_count", 0))
            last_order_count = int(api_data.get("last_order_count", 0))

            # 转换为中文产品名称用于日志显示
            chinese_name = PRODUCT_NAME_MAPPING.get(product_name, product_name)

            # 只有当昨日数据不为0时才保存
            if last_order_sum > 0 or last_reg_count > 0 or last_order_count > 0:
                return self.save_yesterday_data(
                    product_name, last_order_sum, last_reg_count, last_order_count
                )
            else:
                print(f"产品 {chinese_name} 的昨日数据为空，跳过保存")
                return False

        except Exception as e:
            print(f"处理API响应数据时出错: {e}")
            return False


# 创建全局实例
daily_data_manager = DailyDataManager()


def save_yesterday_data_from_api(api_data: Dict, product_name: str) -> bool:
    """从API数据中保存昨日数据的便捷函数"""
    return daily_data_manager.process_api_response_data(api_data, product_name)


def is_yesterday_data_exists(product_name: str) -> bool:
    """检查昨日数据是否已存在的便捷函数"""
    return daily_data_manager.is_yesterday_data_saved(product_name)


def get_recent_7days_statistics(product_name: str) -> Dict:
    """获取近7日统计数据的便捷函数"""
    return daily_data_manager.get_recent_7days_stats(product_name)
